<?php

/**
 * @version		$Id: hello.php 15 2009-11-02 18:37:15Z chdemko $
 * @package		Joomla16.Tutorials
 * @subpackage	Components
 * @copyright	Copyright (C) 2005 - 2010 Open Source Matters, Inc. All rights reserved.
 * @author		Christophe Demko
 * @link		http://joomlacode.org/gf/project/helloworld_1_6/
 * @license		License GNU General Public License version 2 or later
 */

/* No direct access to this file */
defined('_JEXEC') or die('Restricted access');

/* import Joomla modelitem library */
jimport('joomla.application.component.modelitem');

/* clss bgModelbg  */
class bgModelbg extends JModel

{	var $_total = null;
    var $_pagination = null;
    
    public function __construct(){
    	parent::__construct();
 
        $mainframe = JFactory::getApplication();
 
        /* Get pagination request variables */
        $limit = $mainframe->getUserStateFromRequest('com_questionare.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
        $limitstart = JRequest::getVar('limitstart', 0, '', 'int');
 
        /* In case limit has been changed, adjust it */
        $limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);
 
        $this->setState('limit', $limit);
        $this->setState('limitstart', $limitstart);
    }
    
    /* get all records club */
	function getAll($list = null){

        $qr = "SELECT * FROM serq";
        $where = array();
       /* search */
       
       if(isset($list['filter_search']) && $list['filter_search']){
            $where[] = "SERQ_TITLE LIKE '%$list[filter_search]%'";
       } 
       
       if($where){
            $where = ' WHERE '.implode(' AND ', $where);
        }else{
            $where = null;
        }
       /* get total number record */
       $this->_total = $this->getTotal($where);
       
       $qr .= $where;
       /* order by */
       $qr .= " ORDER BY SERQ_CODE DESC"; 
       
       /* pagination */
       $qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');
       /* load data */
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
 
       return $result;
       
	}

    /* get total records */
    function getTotal($where = null){
        /* Load the content if it doesn't already exist */
        if ($this->_total === null) {
            $query = "SELECT count(*) FROM serq $where";
            $this->_db->setQuery($query);
            $this->_total = $this->_db->loadResult();
        }
        return $this->_total;
    } 
    
    function getPagination(){
        /* Load the content if it doesn't already exist */
        if (empty($this->_pagination)) {
            jimport('joomla.html.pagination');
            $this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );
        }
        return $this->_pagination;
    }
    
    
    
    /* Check ID exist */
    public function checkExistID($cid){
        $qr = "SELECT SERQ_CODE FROM serq WHERE SERQ_CODE = $cid";
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
        if(!empty($result)){ 
            return true;
        }
        return false;
    }
    
    /* count number anwser in questionare */
    public function countAnsQuestionare($id) {
        $query = "SELECT COUNT(DISTINCT SESS_CODE) FROM sess
                    INNER JOIN `resq` ON `RESQ_SESS_CODE` = `SESS_CODE`
                    INNER JOIN `kc2vr_users` ON `id` = `SESS_USER_CODE`
                 WHERE SESS_SERQ_CODE = $id";
                 
        $this->_db->setQuery($query);
        $this->_total = $this->_db->loadResult();
        
        return $this->_total;
    }
    
    /* load all records with id */
    public function load($cid){
        $query = "SELECT * FROM serq WHERE SERQ_CODE=($cid)";
        
        $this->_db->setQuery($query);
        $result = $this->_db->loadObject();
        
        return $result;
    }
    
    /* save */
     public function save($data, $addNew){
        if($addNew == true)
        {
            /* insert values to club table */
            $qr = "INSERT INTO serq (SERQ_CODE, SERQ_USER_CREATE, SERQ_TITLE, SERQ_DESCRIPTION, SERQ_IMG, SERQ_SEND_USER_ID_LIST, SERQ_PUBLISHED
            , SERQ_END_TITLE, SERQ_END_DESCRIPTION, SERQ_START, SERQ_FINISH, SERQ_FLAG) VALUES ('$data[SERQ_CODE]', '$data[SERQ_USER_CREATE]',
             '$data[SERQ_TITLE]', '$data[SERQ_DESCRIPTION]', '$data[SERQ_IMG]', '$data[SERQ_SEND_USER_ID_LIST]', '$data[SERQ_PUBLISHED]', '$data[SERQ_END_TITLE]'
             ,'$data[SERQ_END_DESCRIPTION]', '$data[SERQ_START]', '$data[SERQ_FINISH]', '$data[SERQ_FLAG]')";
          
            $this->_db->setQuery($qr);
            if($this->_db->query())
                return true;
            else
                return false;
        } else {
             /* update valuse to pagq table */
            $qr = "UPDATE serq SET SERQ_TITLE = '$data[SERQ_TITLE]', SERQ_DESCRIPTION = '$data[SERQ_DESCRIPTION]',
            SERQ_IMG = '$data[SERQ_IMG]', SERQ_SEND_USER_ID_LIST = '$data[SERQ_SEND_USER_ID_LIST]' , SERQ_END_TITLE = '$data[SERQ_END_TITLE]', SERQ_END_DESCRIPTION = '$data[SERQ_END_DESCRIPTION]'
            , SERQ_START = '$data[SERQ_START]', SERQ_FINISH = '$data[SERQ_FINISH]', SERQ_PUBLISHED = '$data[SERQ_PUBLISHED]', SERQ_USER_CREATE = '$data[SERQ_USER_CREATE]'
            , SERQ_FLAG = '$data[SERQ_FLAG]' WHERE SERQ_CODE = '$data[SERQ_CODE]'";
          
            $this->_db->setQuery($qr);
            if($this->_db->query())
                return true;
            else
                return false;
        }
        
     }
    /* remove serq items */
    public function remove($cid){
        $cid = implode(',', $cid);
    
        $query = "DELETE FROM serq WHERE SERQ_CODE IN($cid)";
  
        $this->_db->setQuery($query);
        $this->_db->query();
    }
    /**
     * Remove pagq from SERQ_CODE 
    */
    public function removepagq($cid){
        $cid = implode(',', $cid);
    
        $query = "DELETE FROM pagq WHERE PAGQ_SERQ_CODE IN($cid)";
  
        $this->_db->setQuery($query);
        $this->_db->query();
    }
    /**
     * Remove QUES from SERQ_CODE 
    */
    public function removeques($cid){
        $cid = implode(',', $cid);
    
        $query = "DELETE FROM ques WHERE QUES_SERQ_CODE IN($cid)";
  
        $this->_db->setQuery($query);
        $this->_db->query();
    }
    
    /**
     * Remove ANSQ from QUES_CODE 
    */
    public function removeansq($cid){
        $cid = implode(',', $cid);
    
        $query = "DELETE FROM ansq WHERE ANSQ_QUES_CODE IN($cid)";
  
        $this->_db->setQuery($query);
        $this->_db->query();
    }

    /**
     * Remove RESQ from RESQ_SESS_CODE 
    */
    public function removeresq($cid){
        $cid = implode(',', $cid);
        $query = "DELETE FROM resq
                        WHERE RESQ_SESS_CODE IN (
                                    SELECT  SESS_CODE
                                    FROM    sess
                                    WHERE   SESS_SERQ_CODE IN($cid))";
        $this->_db->setQuery($query);
        $this->_db->query();
    }

    /**
     * Remove SESS from SESS_SERQ_CODE 
    */
    public function removesess($cid){
        $cid = implode(',', $cid);
    
        $query = "DELETE FROM sess WHERE SESS_SERQ_CODE IN($cid)";
  
        $this->_db->setQuery($query);
        $this->_db->query();
    }
    
    /**
     * GET IMAGE FROM PAGQ_CODE 
    **/
    public function getImgFromPAGQ_CODE($cid){
        $qr = "SELECT PAGQ_IMG FROM pagq WHERE PAGQ_CODE = $cid";
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
       return $result;
    }
    
    /* pop up */
    function getAllUsertype(){
	   $qr = "SELECT id,title FROM #__usergroups LIMIT 7,3";
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
     
       return $result;
	}
    
    /* summary question  */
    function getQuestion($id){
        $qr = "SELECT QUES_CODE, QUES_TITLE,QUES_TYPE FROM ques WHERE QUES_SERQ_CODE = $id ORDER BY QUES_CODE";
        $this->_db->setQuery($qr);
        $result = $this->_db->loadObjectList();
        
        return $result;
    }
    
    
    /* summary anwser  */
    function getAnwser($id){
        $qr = "SELECT ANSQ_CODE, ANSQ_VALUE FROM ansq WHERE ANSQ_QUES_CODE= $id ORDER BY ANSQ_CODE ASC";
        $this->_db->setQuery($qr);
        $result = $this->_db->loadObjectList();
     
        return $result;
    }
    
    /* get anwser every question */
    function getAns($res_code,$res_value){
        $qr = "SELECT count(*) FROM resq 
            INNER JOIN `sess` ON `SESS_CODE` = `RESQ_SESS_CODE`
            INNER JOIN `kc2vr_users` ON `id` = `SESS_USER_CODE`
            WHERE RESQ_QUES_CODE = $res_code AND RESQ_VALUE = '$res_value'";
        $this->_db->setQuery($qr);
        $this->_total = $this->_db->loadResult();
        
        return  $this->_total;
    }
    
    /* get all anwser */
    function getTotalAns($id){
        $qr = "SELECT COUNT(DISTINCT `RESQ_QUES_CODE`, `RESQ_SESS_CODE`) FROM resq
                INNER JOIN `sess` ON `SESS_CODE` = `RESQ_SESS_CODE`
                INNER JOIN `kc2vr_users` ON `id` = `SESS_USER_CODE`
                WHERE RESQ_QUES_CODE = $id";
        $this->_db->setQuery($qr);
        echo $qr; die;
        $this->_total = $this->_db->loadResult();
        
        return  $this->_total;
    }
   /* get all mail group */ 
   function getAllMailGroup(){
	   $qr = "SELECT MAGR_CODE,MAGR_NAME FROM magr";
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
	}
    
    function getAllArea(){
	   $qr = "SELECT AREA_CODE,AREA_NAME FROM area ORDER BY AREA_CODE ASC";
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
	}
    
    function getAllPrefOfArea($area_code){
       
       if($area_code == 0){
            $qr = "SELECT PREF_CODE,PREF_NAME FROM pref";
        }else{
            $qr = "SELECT PREF_CODE,PREF_NAME FROM pref WHERE PREF_AREA_CODE=".$area_code;     
        }
        
        $this->_db->setQuery($qr);
        $result = $this->_db->loadObjectList();
       return $result;
	}
    
    function getAllUserMail($filter){
        $sql_="";
        
        $sub_select = "(SELECT DISTINCT OCBA_CODE, OCBA_NAME, OCBA_TOWN_CODE, TOWN_PREF_CODE, PREF_CODE, PREF_AREA_CODE
                        FROM ocba
                        JOIN town ON OCBA_TOWN_CODE = TOWN_CODE
                        JOIN pref ON TOWN_PREF_CODE = PREF_CODE
                        WHERE OCBA_FLAG = 1 ) AS ocba";
        $sql_user = "SELECT id, name, email, OCBA_CODE, OCBA_NAME,PREF_CODE, PREF_AREA_CODE, group_id, sendEmail as mail_flag FROM kc2vr_users 
                    JOIN ".$sub_select." ON USEM_OCBA_CODE = OCBA_CODE
                    JOIN kc2vr_user_usergroup_map on id = user_id  ";
                    
       
        switch($filter['usertype_search']){
            case 'all':
                $sql_ = $sql_user." UNION ".$sql_coch;
                break;
            case 'office':
                $sql_ = $sql_user." AND group_id IN (8,9,10) ";
                break;
        
            case 'center':
                $sql_ = $sql_user." AND group_id IN (11,12) AND LEFT(OCBA_CODE,1)='0'";
                break;
            case 'club':
                $sql_ = $sql_user." AND group_id IN (11,12) AND LEFT(OCBA_CODE,1)='1' ";
                break;
        }

        $sql = "SELECT * FROM ($sql_) as result WHERE 1 = 1 " ;           
        
        if($filter['area_search'] != 0){ // neu thanh pho k duoc chon, khu vuc duoc chon thi select tat ca trung tam thuoc khu vuc do
            $sql.=" AND PREF_AREA_CODE = ".$filter['area_search'];
        }
        
        if($filter['pref_search'] != 0){  // neu thanh pho duoc chon thi chi select tat ca trung tam thuoc thanh pho do
            $sql.=" AND PREF_CODE = ".$filter['pref_search'];
        }
        
        if($filter['ocba_name'] != ''){ // search gan dung voi ten trung tam
            $sql.=" AND (OCBA_NAME LIKE '%".$filter['ocba_name']."%' OR OCBA_KANA LIKE '%".$filter['ocba_name']."%')";
        }   
        //echo "<p>$sql</p>";   
        $this->_db->setQuery($sql);
        $result = $this->_db->loadObjectList();
        return $result;
    }
    
   
    
    function checkUserType($key){
        $qr = "SELECT MAGR_USERTYPE FROM magr WHERE MAGR_CODE=".$key;
                
        $this->_db->setQuery($qr);
        $result = $this->_db->loadObject();
        return $result->MAGR_USERTYPE;
    }
    
    /**
     * Load questionare from SERQ_CODE 
    */
    public function loadSerq($cid){
        
        $query = "SELECT * FROM serq WHERE SERQ_CODE = $cid";
        
        $this->_db->setQuery($query);
        $result = $this->_db->loadObject();
        
        return $result;
    }
    
    /**
     * Load question page from SERQ_CODE 
    */
    public function loadPagqFromSerq($cid){
        
        $query = "SELECT * FROM pagq WHERE PAGQ_SERQ_CODE = $cid";
        
        $this->_db->setQuery($query);
        $result = $this->_db->loadObjectList();
        
        return $result;
    }
    
    /**
     * Load question from SERQ_CODE 
    */
    public function loadQuesFromSerq($cid){
        
        $query = "SELECT * FROM ques WHERE QUES_SERQ_CODE = $cid";
        
        $this->_db->setQuery($query);
        $result = $this->_db->loadObjectList();
        
        return $result;
    }
    
    /**
     * Load question from SERQ_CODE 
    */
    public function loadAnsqFromQues($cid){
        
        $query = "SELECT * FROM ansq WHERE ANSQ_QUES_CODE = $cid";
        
        $this->_db->setQuery($query);
        $result = $this->_db->loadObjectList();
        
        return $result;
    }
    
    /**
     * save copy serq 
    */
    public function copyserq($data){
        
        $qr = "INSERT INTO serq (SERQ_CODE, SERQ_USER_CREATE, SERQ_TITLE, SERQ_DESCRIPTION, SERQ_IMG, SERQ_SEND_USER_ID_LIST, SERQ_PUBLISHED
                                , SERQ_END_TITLE, SERQ_END_DESCRIPTION, SERQ_START, SERQ_FINISH, SERQ_FLAG) VALUES ('".$data->SERQ_CODE."', '".$data->SERQ_USER_CREATE."',
                                 '".$data->SERQ_TITLE."', '".$data->SERQ_DESCRIPTION."', '".$data->SERQ_IMG."', '".$data->MAIL_USER."', '".$data->SERQ_PUBLISHED."', '".$data->SERQ_END_TITLE."'
                                 ,'".$data->SERQ_END_DESCRIPTION."', '".$data->SERQ_START."', '".$data->SERQ_FINISH."', '".$data->SERQ_FLAG."')";
      
        $this->_db->setQuery($qr);
        if($this->_db->query())
            return true;
        else
            return false;
        
     }
     
     /**
     * save copy pagq
    */
    public function copypagq($data){
        
        $qr = "INSERT INTO pagq (PAGQ_CODE, PAGQ_SERQ_CODE, PAGQ_TITLE, PAGQ_DESCRIPTION, PAGQ_IMG, PAGQ_PUBLISHED) 
                        VALUES ";
        
        $val = "('".$data[0]->PAGQ_CODE."','".$data[0]->PAGQ_SERQ_CODE."','".$data[0]->PAGQ_TITLE."','".$data[0]->PAGQ_DESCRIPTION."','".$data[0]->PAGQ_IMG."','".$data[0]->PAGQ_PUBLISHED."')";
        
        for($i = 1; $i < count($data); $i++){
            
            if($data[$i] != null && $data[$i]->PAGQ_CODE != null){
                $val .= ", ('".$data[$i]->PAGQ_CODE."','".$data[$i]->PAGQ_SERQ_CODE."','".$data[$i]->PAGQ_TITLE."','".$data[$i]->PAGQ_DESCRIPTION."','".$data[$i]->PAGQ_IMG."','".$data[$i]->PAGQ_PUBLISHED."')";
            }
        }
        //create query
        $qr .= $val;
        
        $this->_db->setQuery($qr);
        if($this->_db->query())
            return true;
        else
            return false;
        
     }
     
     /**
     * save copy question
    */
    public function copyques($data){
        
        $qr = "INSERT INTO ques (QUES_CODE,QUES_SERQ_CODE,QUES_TITLE,QUES_REQUIRED,QUES_DESCRIPTION,QUES_TYPE,QUES_PAGQ_CODE,QUES_PUBLISHED) 
                VALUES ";
        
        $val = "('".$data[0]->QUES_CODE."','".$data[0]->QUES_SERQ_CODE."','".$data[0]->QUES_TITLE."','".$data[0]->QUES_REQUIRED."','".$data[0]->QUES_DESCRIPTION."','".$data[0]->QUES_TYPE."','".$data[0]->QUES_PAGQ_CODE."','".$data[0]->QUES_PUBLISHED."')";
        
        for($i = 1; $i < count($data); $i++){
            
            if($data[$i] != null && $data[$i]->QUES_CODE != null){
                $val .= ", ('".$data[$i]->QUES_CODE."','".$data[$i]->QUES_SERQ_CODE."','".$data[$i]->QUES_TITLE."','".$data[$i]->QUES_REQUIRED."','".$data[$i]->QUES_DESCRIPTION."','".$data[$i]->QUES_TYPE."','".$data[$i]->QUES_PAGQ_CODE."','".$data[$i]->QUES_PUBLISHED."')";
            }
        }
        //create query
        $qr .= $val;
        
        $this->_db->setQuery($qr);
        if($this->_db->query())
            return true;
        else
            return false;
        
     }
     
     /**
     * save copy answer
    */
    public function copyansq($data){
        
        $qr = "INSERT INTO ansq (ANSQ_CODE,ANSQ_QUES_CODE,ANSQ_VALUE) 
                                            VALUES ";
        
        $val = "('".$data[0]->ANSQ_CODE."','".$data[0]->ANSQ_QUES_CODE."','".$data[0]->ANSQ_VALUE."')";
        
        for($i = 1; $i < count($data); $i++){
            
            if($data[$i] != null && $data[$i]->ANSQ_CODE != null){
                $val .= ", ('".$data[$i]->ANSQ_CODE."','".$data[$i]->ANSQ_QUES_CODE."','".$data[$i]->ANSQ_VALUE."')";
            }
        }
        //create query
        $qr .= $val;
        
        $this->_db->setQuery($qr);
        if($this->_db->query())
            return true;
        else
            return false;
        
     }
     
    /**
     * GET IMAGE FROM SERQ_CODE 
    **/
    public function getImgFromSERQ_CODE($cid){
        $qr = "SELECT SERQ_IMG FROM serq WHERE SERQ_CODE = $cid";
       // load data
       $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();
       
       return $result;
    }
}
